﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Adams.Admin.DataAccess.Sitemap
{
    public static class Web
    {
        public static Adams.Admin.DataTransfer.Sitemap.Web.PageLoad PageLoad()
        {
            var data = new Adams.Admin.DataTransfer.Sitemap.Web.PageLoad();
            var topnodes = new Shared.Entities.Sitemap.WebTopNodeCollection();
            var leftnodes = new Shared.Entities.Sitemap.WebLeftNodeCollection();
            var bottomnodes = new Shared.Entities.Sitemap.WebBottomNodeCollection();

            using (SqlDataReader dr = DB.RunDataReader(@"
WITH AllNodes AS (
	SELECT n.NodeID, n.ParentNodeID, n.DisplayOrder, 0 AS [Level], n.Title, n.Url
	FROM Sitemap.WebTopNodes n
	WHERE NodeID = 1
	UNION ALL
	SELECT n.NodeID, n.ParentNodeID, n.DisplayOrder, a.[Level] + 1, n.Title, n.Url
	FROM Sitemap.WebTopNodes n JOIN AllNodes a ON n.ParentNodeID = a.NodeID 
)
SELECT * 
FROM AllNodes
ORDER BY Level, DisplayOrder;

WITH AllNodes AS (
	SELECT n.NodeID, n.ParentNodeID, n.DisplayOrder, 0 AS [Level], n.Title, n.Url
	FROM Sitemap.WebLeftNodes n
	WHERE NodeID = 1
	UNION ALL
	SELECT n.NodeID, n.ParentNodeID, n.DisplayOrder, a.[Level] + 1, n.Title, n.Url
	FROM Sitemap.WebLeftNodes n JOIN AllNodes a ON n.ParentNodeID = a.NodeID 
)
SELECT * 
FROM AllNodes
ORDER BY Level, DisplayOrder;

WITH AllNodes AS (
	SELECT n.NodeID, n.ParentNodeID, n.DisplayOrder, 0 AS [Level], n.Title, n.Url
	FROM Sitemap.WebBottomNodes n
	WHERE NodeID = 1
	UNION ALL
	SELECT n.NodeID, n.ParentNodeID, n.DisplayOrder, a.[Level] + 1, n.Title, n.Url
	FROM Sitemap.WebBottomNodes n JOIN AllNodes a ON n.ParentNodeID = a.NodeID 
)
SELECT * 
FROM AllNodes
ORDER BY Level, DisplayOrder;
", CommandType.Text))
            {
                while (dr.Read())
                {
                    var node = new Shared.Entities.Sitemap.WebTopNode();
                    node.ID = dr["NodeID"].DbInt();
                    node.Url = dr["Url"].DbString();
                    node.Title = dr["Title"].DbString();
                    node.DisplayOrder = dr["DisplayOrder"].DbInt();
                    node.ParentNode = new Shared.Entities.Sitemap.WebTopNode() {
                        ID = dr["ParentNodeID"].DbInt()
                    };
                    node.ChildNodes = new Shared.Entities.Sitemap.WebTopNodeCollection();
                    topnodes.Add(node);
                }

                dr.NextResult();
                while (dr.Read())
                {
                    var node = new Shared.Entities.Sitemap.WebLeftNode();
                    node.ID = dr["NodeID"].DbInt();
                    node.Url = dr["Url"].DbString();
                    node.Title = dr["Title"].DbString();
                    node.DisplayOrder = dr["DisplayOrder"].DbInt();
                    node.ParentNode = new Shared.Entities.Sitemap.WebLeftNode()
                    {
                        ID = dr["ParentNodeID"].DbInt()
                    };
                    node.ChildNodes = new Shared.Entities.Sitemap.WebLeftNodeCollection();
                    leftnodes.Add(node);
                }

                dr.NextResult();
                while (dr.Read())
                {
                    var node = new Shared.Entities.Sitemap.WebBottomNode();
                    node.ID = dr["NodeID"].DbInt();
                    node.Url = dr["Url"].DbString();
                    node.Title = dr["Title"].DbString();
                    node.DisplayOrder = dr["DisplayOrder"].DbInt();
                    node.ParentNode = new Shared.Entities.Sitemap.WebBottomNode()
                    {
                        ID = dr["ParentNodeID"].DbInt()
                    };
                    node.ChildNodes = new Shared.Entities.Sitemap.WebBottomNodeCollection();
                    bottomnodes.Add(node);
                }
            }

            foreach (var node in topnodes)
            {
                if (node.ID == 1)
                {
                    data.TopHomeNode = node;
                }
                else
                {
                    topnodes.Where(n => n.ID == node.ParentNode.ID).FirstOrDefault().ChildNodes.Add(node);
                }
            }

            foreach (var node in leftnodes)
            {
                if (node.ID == 1)
                {
                    data.LeftHomeNode = node;
                }
                else
                {
                    leftnodes.Where(n => n.ID == node.ParentNode.ID).FirstOrDefault().ChildNodes.Add(node);
                }
            }

            foreach (var node in bottomnodes)
            {
                if (node.ID == 1)
                {
                    data.BottomHomeNode = node;
                }
                else
                {
                    bottomnodes.Where(n => n.ID == node.ParentNode.ID).FirstOrDefault().ChildNodes.Add(node);
                }
            }

            return data;
        }

        public static void UpdateTop(Adams.Admin.DataTransfer.Sitemap.Web.UpdateTopInput input)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var node in input.Nodes)
            {
                sb.Append(@"
UPDATE Sitemap.WebTopNodes SET ParentNodeID = @ParentNodeID, DisplayOrder = " + node.DisplayOrder + @" WHERE NodeID = " + node.ID + @"
");
            }

            DB.Run(sb.ToString(), CommandType.Text,
                DB.CreateParameter("@ParentNodeID", input.ParentNodeID));
        }

        public static Adams.Admin.DataTransfer.Sitemap.Web.GetTopNode GetTopNode(Adams.Admin.DataTransfer.Sitemap.Web.GetTopNodeInput input)
        {
            var data = new Adams.Admin.DataTransfer.Sitemap.Web.GetTopNode();
            
            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT * 
FROM Sitemap.WebTopNodes 
WHERE NodeID = @NodeID
", CommandType.Text,
    DB.CreateParameter("@NodeID", input.NodeID)))
            {
                while (dr.Read())
                {
                    var node = new Shared.Entities.Sitemap.WebTopNode();
                    node.ID = dr["NodeID"].DbInt();
                    node.Url = dr["Url"].DbString();
                    node.Title = dr["Title"].DbString();
                    node.DisplayOrder = dr["DisplayOrder"].DbInt();
                    data.Node = node;
                }
            }

            return data;
        }

        public static int SaveTopNode(DataTransfer.Sitemap.Web.NodeTopFormInput input)
        {
            return DB.RunScalar(@"
IF(@NodeID = 0)
BEGIN
    INSERT Sitemap.WebTopNodes (ParentNodeID, Title, Url, DisplayOrder)
    VALUES (@ParentNodeID, @Title, @Url, @DisplayOrder)
    SELECT SCOPE_IDENTITY() AS NodeID
END
ELSE
BEGIN
    UPDATE Sitemap.WebTopNodes
    SET Title = @Title, Url = @Url
    WHERE NodeID = @NodeID
    SELECT @NodeID AS NodeID
END
", CommandType.Text,
                DB.CreateParameter("@NodeID", input.ID),
                DB.CreateParameter("@ParentNodeID", input.ParentNodeID.ToNString()),
                DB.CreateParameter("@DisplayOrder", input.DisplayOrder.ToNString()),
                DB.CreateParameter("@Title", input.Title),
                DB.CreateParameter("@Url", input.Url)).DbInt();
        }

        public static Adams.Admin.DataTransfer.Sitemap.Web.WebTopNodeInfo GetTopNodeInfo(string url, int nodeId)
        {
            var data = new Adams.Admin.DataTransfer.Sitemap.Web.WebTopNodeInfo();

            using (SqlDataReader dr = DB.RunDataReader(@"
IF (@NodeID > 0)
BEGIN
    SELECT Url
    FROM Sitemap.WebTopNodes
    WHERE NodeID = @NodeID
END

SELECT COUNT(*) AS Count
FROM Sitemap.WebTopNodes
WHERE Url = @Url
", CommandType.Text,
                DB.CreateParameter("@NodeID", nodeId),
                DB.CreateParameter("@Url", url)))
            {
                if (nodeId > 0)
                {
                    dr.Read();
                    data.Url = dr["Url"].DbString();
                    dr.NextResult();
                }
                dr.Read();
                data.Exists = dr["Count"].DbInt() > 0;
            }

            return data;
        }

        public static void DeleteTopNode(int nodeId)
        {
            DB.Run(@"
DELETE Sitemap.WebTopNodes WHERE NodeID = @NodeID
", CommandType.Text,
    DB.CreateParameter("@NodeID", nodeId));
        }

        public static void UpdateLeft(Adams.Admin.DataTransfer.Sitemap.Web.UpdateLeftInput input)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var node in input.Nodes)
            {
                sb.Append(@"
UPDATE Sitemap.WebLeftNodes SET ParentNodeID = @ParentNodeID, DisplayOrder = " + node.DisplayOrder + @" WHERE NodeID = " + node.ID + @"
");
            }

            DB.Run(sb.ToString(), CommandType.Text,
                DB.CreateParameter("@ParentNodeID", input.ParentNodeID));
        }

        public static Adams.Admin.DataTransfer.Sitemap.Web.GetLeftNode GetLeftNode(Adams.Admin.DataTransfer.Sitemap.Web.GetLeftNodeInput input)
        {
            var data = new Adams.Admin.DataTransfer.Sitemap.Web.GetLeftNode();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT * 
FROM Sitemap.WebLeftNodes 
WHERE NodeID = @NodeID
", CommandType.Text,
    DB.CreateParameter("@NodeID", input.NodeID)))
            {
                while (dr.Read())
                {
                    var node = new Shared.Entities.Sitemap.WebLeftNode();
                    node.ID = dr["NodeID"].DbInt();
                    node.Url = dr["Url"].DbString();
                    node.Title = dr["Title"].DbString();
                    node.DisplayOrder = dr["DisplayOrder"].DbInt();
                    data.Node = node;
                }
            }

            return data;
        }

        public static int SaveLeftNode(DataTransfer.Sitemap.Web.NodeLeftFormInput input)
        {
            return DB.RunScalar(@"
IF(@NodeID = 0)
BEGIN
    INSERT Sitemap.WebLeftNodes (ParentNodeID, Title, Url, DisplayOrder)
    VALUES (@ParentNodeID, @Title, @Url, @DisplayOrder)
    SELECT SCOPE_IDENTITY() AS NodeID
END
ELSE
BEGIN
    UPDATE Sitemap.WebLeftNodes
    SET Title = @Title, Url = @Url
    WHERE NodeID = @NodeID
    SELECT @NodeID AS NodeID
END
", CommandType.Text,
                DB.CreateParameter("@NodeID", input.ID),
                DB.CreateParameter("@ParentNodeID", input.ParentNodeID.ToNString()),
                DB.CreateParameter("@DisplayOrder", input.DisplayOrder.ToNString()),
                DB.CreateParameter("@Title", input.Title),
                DB.CreateParameter("@Url", input.Url)).DbInt();
        }

        public static Adams.Admin.DataTransfer.Sitemap.Web.WebLeftNodeInfo GetLeftNodeInfo(string url, int nodeId)
        {
            var data = new Adams.Admin.DataTransfer.Sitemap.Web.WebLeftNodeInfo();

            using (SqlDataReader dr = DB.RunDataReader(@"
IF (@NodeID > 0)
BEGIN
    SELECT Url
    FROM Sitemap.WebLeftNodes
    WHERE NodeID = @NodeID
END

SELECT COUNT(*) AS Count
FROM Sitemap.WebLeftNodes
WHERE Url = @Url
", CommandType.Text,
                DB.CreateParameter("@NodeID", nodeId),
                DB.CreateParameter("@Url", url)))
            {
                if (nodeId > 0)
                {
                    dr.Read();
                    data.Url = dr["Url"].DbString();
                    dr.NextResult();
                }
                dr.Read();
                data.Exists = dr["Count"].DbInt() > 0;
            }

            return data;
        }

        public static void DeleteLeftNode(int nodeId)
        {
            DB.Run(@"
DELETE Sitemap.WebLeftNodes WHERE NodeID = @NodeID
", CommandType.Text,
    DB.CreateParameter("@NodeID", nodeId));
        }

        public static void UpdateBottom(Adams.Admin.DataTransfer.Sitemap.Web.UpdateBottomInput input)
        {
            StringBuilder sb = new StringBuilder();
            foreach (var node in input.Nodes)
            {
                sb.Append(@"
UPDATE Sitemap.WebBottomNodes SET ParentNodeID = @ParentNodeID, DisplayOrder = " + node.DisplayOrder + @" WHERE NodeID = " + node.ID + @"
");
            }

            DB.Run(sb.ToString(), CommandType.Text,
                DB.CreateParameter("@ParentNodeID", input.ParentNodeID));
        }

        public static Adams.Admin.DataTransfer.Sitemap.Web.GetBottomNode GetBottomNode(Adams.Admin.DataTransfer.Sitemap.Web.GetBottomNodeInput input)
        {
            var data = new Adams.Admin.DataTransfer.Sitemap.Web.GetBottomNode();

            using (SqlDataReader dr = DB.RunDataReader(@"
SELECT * 
FROM Sitemap.WebBottomNodes 
WHERE NodeID = @NodeID
", CommandType.Text,
    DB.CreateParameter("@NodeID", input.NodeID)))
            {
                while (dr.Read())
                {
                    var node = new Shared.Entities.Sitemap.WebBottomNode();
                    node.ID = dr["NodeID"].DbInt();
                    node.Url = dr["Url"].DbString();
                    node.Title = dr["Title"].DbString();
                    node.DisplayOrder = dr["DisplayOrder"].DbInt();
                    data.Node = node;
                }
            }

            return data;
        }

        public static int SaveBottomNode(DataTransfer.Sitemap.Web.NodeBottomFormInput input)
        {
            return DB.RunScalar(@"
IF(@NodeID = 0)
BEGIN
    INSERT Sitemap.WebBottomNodes (ParentNodeID, Title, Url, DisplayOrder)
    VALUES (@ParentNodeID, @Title, @Url, @DisplayOrder)
    SELECT SCOPE_IDENTITY() AS NodeID
END
ELSE
BEGIN
    UPDATE Sitemap.WebBottomNodes
    SET Title = @Title, Url = @Url
    WHERE NodeID = @NodeID
    SELECT @NodeID AS NodeID
END
", CommandType.Text,
                DB.CreateParameter("@NodeID", input.ID),
                DB.CreateParameter("@ParentNodeID", input.ParentNodeID.ToNString()),
                DB.CreateParameter("@DisplayOrder", input.DisplayOrder.ToNString()),
                DB.CreateParameter("@Title", input.Title),
                DB.CreateParameter("@Url", input.Url)).DbInt();
        }

        public static Adams.Admin.DataTransfer.Sitemap.Web.WebBottomNodeInfo GetBottomNodeInfo(string url, int nodeId)
        {
            var data = new Adams.Admin.DataTransfer.Sitemap.Web.WebBottomNodeInfo();

            using (SqlDataReader dr = DB.RunDataReader(@"
IF (@NodeID > 0)
BEGIN
    SELECT Url
    FROM Sitemap.WebBottomNodes
    WHERE NodeID = @NodeID
END

SELECT COUNT(*) AS Count
FROM Sitemap.WebBottomNodes
WHERE Url = @Url
", CommandType.Text,
                DB.CreateParameter("@NodeID", nodeId),
                DB.CreateParameter("@Url", url)))
            {
                if (nodeId > 0)
                {
                    dr.Read();
                    data.Url = dr["Url"].DbString();
                    dr.NextResult();
                }
                dr.Read();
                data.Exists = dr["Count"].DbInt() > 0;
            }

            return data;
        }

        public static void DeleteBottomNode(int nodeId)
        {
            DB.Run(@"
DELETE Sitemap.WebBottomNodes WHERE NodeID = @NodeID
", CommandType.Text,
    DB.CreateParameter("@NodeID", nodeId));
        }
    }
}
